# from subtitle import mysqldb
import mysqldb
import os
import datetime

# 获取任务列表
def getTaskList(corpid, userid, page='1', pagesize='12'):
    # 根据设置来判断是否需要重建subtask
    cflag = os.environ.get("CREATE_TABLE")
    if(cflag == 'YES'):
        mysqldb.createSubtask()
        os.environ["CREATE_TABLE"] = "NO"
    sql = f"select * from subtask where corpid='{corpid}' and cman={userid} and status != 0 order by id desc"
    list = mysqldb.fetchJsonList("subtask" , sql)
    tlen = len(list)
    i_page = int(page) - 1
    if(i_page < 0):
        i_page = 0

    i_pagesize = int(pagesize)
    sbegin = i_page * i_pagesize
    return list[sbegin:sbegin+i_pagesize], tlen

# 获取任务状态
def getSubtitleTaskStatus(corpid, userid, taskid):
    sql = f"select * from subtask where corpid='{corpid}' and cman={userid} and id = {taskid}"
    list = mysqldb.fetchJsonList("subtask" , sql)
    return list

# 设置任务的进度
def setSubtitleTaskProcess(uuid, val,  step, process, status = 1):
    sql = f"update subtask set process={process}, status = {status} where {uuid} = '{val}' and step = {step}"
    mysqldb.execUpdate(sql)
    return True

# 设置剪切任务状态 id=id, step=2 process=1
def setSubtitleTaskProcess2(id, status=1):
    sql = f"update subtask set process=1, step=2, status={status} where id = {id}"
    mysqldb.execUpdate(sql)
    return True

# 设置剪切任务失败状态
def setSubtitleTaskFault(id):
    sql = f"update subtask set status=-1 where id = {id}"
    mysqldb.execUpdate(sql)
    return True
    
# 根据SQL获取单个记录
def getSubtitleTask(sql):
    result = mysqldb.fetchOneBySql(sql)
    return result

# 统计24小时内的任务状态
def getStatus(corpid):
    sql = f"SELECT COUNT(CASE WHEN status = -1 THEN 1 END) AS failed, \
        COUNT(CASE WHEN status = 1 AND process = 100 THEN 1 END) AS completed, \
        COUNT(CASE WHEN status = 1 AND process = 0  THEN 1 END) AS waitting, \
        COUNT(CASE WHEN status = 1 AND process > 0 AND process < 100  THEN 1 END) AS processing \
        FROM subtask WHERE ctime >= DATE_SUB(NOW(), INTERVAL 24 HOUR) and corpid={corpid}"
    list = mysqldb.fetchBySql(sql)
    return list

def getSubtitleTask(corpid, uid, taskid):
    sql = f"select * from subtask where corpid ={corpid} and cman = {uid} and id = {taskid}"
    result = mysqldb.fetchOneBySql(sql)
    return result


# 更新subtitle的截图地址
def updateSubtitleThumbnailAndDurationResolution(uuid, url, duration,resolution):
    sql = f"update subtask set thumbnail='{url}', duration={duration}, resolution='{resolution}' where uuid1 = '{uuid}'"
    result = mysqldb.execUpdate(sql)
    return result

# 更新subtitle的访问地址
def updateSubtitleTaskPath(uuid, tpath, reviewurl, suburl, audiourl):
    sql = f"update subtask set videopath='{tpath}', reviewurl='{reviewurl}', subtitleurl='{suburl}', audiourl ='{audiourl}' where uuid1 = '{uuid}'"
    result = mysqldb.execUpdate(sql)
    return result

# 更新outurl的访问地址
def updateSubtitleOutUrl(uuid, url):
    sql = f"update subtask set outurl='{url}' where uuid1 = '{uuid}'"
    result = mysqldb.execUpdate(sql)
    return result

# 根据SQL获取单个记录
def updateSubtitleTask(sql):
    result = mysqldb.execUpdate(sql)
    return result

# 获取字幕任务的处理数据
def getTaskIdList(sql):
    list = mysqldb.fetchBySql(sql)
    return list

# 删除指定任务
def delSubtitleTask(corpid, userid, taskid):
    sql = f"update subtask set status = 0, etime=now() where corpid='{corpid}' and cman={userid} and id = {taskid}"
    mysqldb.execUpdate(sql)
    return True

def updateData(sql):
    mysqldb.execUpdate(sql)
    return True

def updateSubtitleTaskTitle(corpid, userid, taskid, tname):
    sql = f"update subtask set tname = '{tname}'  where corpid='{corpid}' and cman={userid} and id = {taskid}"
    mysqldb.execUpdate(sql)
    return True

# 增加新任务
def addSubtitleTask(task):

    keys = []
    vals = []
    for key, value in task.items():
        keys.append(key)
        vals.append(value)
    skeys = str(keys).replace('[', '').replace(']', '').replace('\'', '')
    svals = str(vals).replace('[', '').replace(']', '').replace('\'now()\'', 'now()')
    sql = f"insert into subtask({skeys}) values({svals})"
    mysqldb.execUpdate(sql)
    return True

# 增加任务处理日志
def addTaskLog(tuuid, logtext, cman = 0):

    sql = f"insert into subtasklog(tuuid, logtext, cman, ctime, type) values(\"{tuuid}\", \"{logtext}\", {cman}, now(), 0)"
    mysqldb.execUpdate(sql)

def getTaskLogs(tuuid):
    sql = f"select * from subtasklog where tuuid = '{tuuid}' and type = 0 order by ctime asc"
    list = mysqldb.fetchJsonList("subtasklog" , sql)
    return list
